# DB-API 2.0 interface for SQLite databases
import sqlite3
import os
# global var
# 数据库文件绝句路
import cx_Oracle
import logging.config
import traceback
from PyQt5.QtCore import QSettings
# from config.OracleConfig import OracleUser, OraclePWD, OracleHost, OraclePort, OracleSql
from cmomon import GET_TXT_SIZE, GET_TXT_QSS
from util.Tools import Tools
DB_FILE_PATH = Tools.resource_path("db\\data.db")
# 是否打印sql
SHOW_SQL = False
settings = QSettings(Tools.resource_path("config\\config.ini"), QSettings.IniFormat)
settings.setIniCodec("UTF-8")
# settings.setIniCodec("GBK")

class ConnectOracle():
    def __init__(self):
        '''
        初始化数据库
        '''
        self.OracleUser = settings.value('cx_Oracle/OracleUser')
        self.OraclePWD = settings.value('cx_Oracle/OraclePWD')
        self.OracleHost = settings.value('cx_Oracle/OracleHost')
        self.OraclePort = settings.value('cx_Oracle/OraclePort')
        self.OracleSql = settings.value('cx_Oracle/OracleSql')
        self.Conn = cx_Oracle.connect('%s/%s@%s:%s/%s' % (self.OracleUser, self.OraclePWD,
                                                          self.OracleHost, self.OraclePort,
                                                          self.OracleSql))
        self.cur = self.Conn.cursor()

    def GetAllDataBySql(self,sql):
        '''
        :param sql:
        :return:
        '''
        try:
            if hasattr(self, "cur"):
                if sql is not None and sql != '':
                    self.cur.execute(sql)
                    return self.cur.fetchone()
                else:
                    print('the [{}] is empty or equal None!'.format(sql))
            else:
                self.Conn = cx_Oracle.connect('%s/%s@%s:%s/%s' % (self.OracleUser, self.OraclePWD,
                                                                  self.OracleHost, self.OraclePort,
                                                                  self.OracleSql))
                self.cur = self.Conn.cursor()
        except Exception:
            logging.error(traceback.format_exc())




    def GetONEDate(self,sql):
        '''

        :param sql:
        :return:
        '''
        try:
            if hasattr(self, "cur"):
                if sql is not None and sql != '':
                    self.cur.execute(sql)
                    return self.cur.fetchone()
                else:
                    print('the [{}] is empty or equal None!'.format(sql))
            else:
                self.Conn = cx_Oracle.connect('%s/%s@%s:%s/%s' % (self.OracleUser, self.OraclePWD,
                                                                  self.OracleHost, self.OraclePort,
                                                                  self.OracleSql))
                self.cur = self.Conn.cursor()
        except Exception:
            logging.error(traceback.format_exc())
    def UpdateImageSql(self,sql):
        '''

        :param sql:
        :param ret:
        :return:
        '''
        try:
            if hasattr(self, "cur"):
                self.Conn.begin()
                self.cur.execute(sql)
                self.Conn.commit()
                return "sucesss"
            else:
                self.Conn = cx_Oracle.connect('%s/%s@%s:%s/%s' % (self.OracleUser, self.OraclePWD,
                                                                  self.OracleHost, self.OraclePort,
                                                                  self.OracleSql))
                self.cur = self.Conn.cursor()
        except Exception:
            logging.error(traceback.format_exc())
            self.Conn.rollback()
    def show_table_table_name(self,sql):
        try:
            if hasattr(self, "cur"):
                if sql is not None and sql != '':
                    self.cur.execute(sql)
                    return self.cur.fetchall()
                else:
                    print('the [{}] is empty or equal None!'.format(sql))
            else:
                self.Conn = cx_Oracle.connect('%s/%s@%s:%s/%s' % (self.OracleUser, self.OraclePWD,
                                                                  self.OracleHost, self.OraclePort,
                                                                  self.OracleSql))
                self.cur = self.Conn.cursor()
        except Exception:
            logging.error(traceback.format_exc())


    def InsertChangeItem(self,sql):
        '''

        :param sql:
        :param ret:
        :return:
        '''
        try:
            if hasattr(self, "cur"):
                self.Conn.begin()
                self.cur.execute(sql)
                self.Conn.commit()
                return "sucesss"
            else:
                self.Conn = cx_Oracle.connect('%s/%s@%s:%s/%s' % (self.OracleUser, self.OraclePWD,
                                                                  self.OracleHost, self.OraclePort,
                                                                  self.OracleSql))
                self.cur = self.Conn.cursor()

        except Exception:
            self.Conn.rollback()
            logging.error(traceback.format_exc())
            return "error"

    def UpdateChangeByPrame(self,sql,date):
        '''

        :param sql:
        :param ret:
        :return:
        '''
        try:
            if hasattr(self, "cur"):
                if sql is not None and sql != '':
                    self.Conn.begin()
                    self.cur.prepare(sql)
                    self.cur.execute(None, date)
                    self.Conn.commit()
                    affected_rows = self.cur.rowcount
                    return affected_rows
                else:
                    print('the [{}] is empty or equal None!'.format(sql))
            else:
                self.Conn = cx_Oracle.connect('%s/%s@%s:%s/%s' % (self.OracleUser, self.OraclePWD,
                                                                  self.OracleHost, self.OraclePort,
                                                                  self.OracleSql))
                self.cur = self.Conn.cursor()

        except Exception as f:
            self.Conn.rollback()
            logging.error(traceback.format_exc())


    def UpdateChangeItem(self,sql):
        '''

        :param sql:
        :param ret:
        :return:
        '''
        try:
            if hasattr(self, "cur"):
                self.Conn.begin()
                self.cur.execute(sql)
                self.Conn.commit()
                return "sucesss"
            else:
                self.Conn = cx_Oracle.connect('%s/%s@%s:%s/%s' % (self.OracleUser, self.OraclePWD,
                                                                  self.OracleHost, self.OraclePort,
                                                                  self.OracleSql))
                self.cur = self.Conn.cursor()
        except Exception:
            self.Conn.rollback()
            logging.error(traceback.format_exc())
            return "error"

    def DeleteItemDate(self,sql,date):
        '''
        删除单个数据
        :param sql:
        :return:
        '''
        try:
            if hasattr(self, "cur"):
                self.Conn.begin()
                self.cur.prepare(sql)
                self.cur.execute(None,date)
                self.Conn.commit()
                return "sucesss"
            else:
                self.Conn = cx_Oracle.connect('%s/%s@%s:%s/%s' % (self.OracleUser, self.OraclePWD,
                                                                  self.OracleHost, self.OraclePort,
                                                                  self.OracleSql))
                self.cur = self.Conn.cursor()
        except Exception:
            self.Conn.rollback()
            logging.error(traceback.format_exc())
            return "error"



